using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Western
{
    public class Securities : DataAccess
    {
        public Securities(string db_str)
        {
            CreateConnection(db_str);
        }
        public DataSet GetLiveBoard(string symbol)
        {
            DataSet live_board = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_sel_LiveBoard";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;

                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(live_board);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return live_board;
        }

        public DataSet GetPriceBoard(string board_id)
        {
            DataSet live_board = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_sel_StockMarket";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters
                cmd.Parameters.Add("@board_id", SqlDbType.VarChar, 10).Value = board_id;

                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(live_board);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return live_board;
        }

        public DataSet GetSecurities(string symbol, DateTime xchg_date)
        {
            DataSet live_board = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from Securities "
                    + "where (symbol = @symbol or @symbol = '') and datediff(day, market_date, @market_date)=0";
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;
                cmd.Parameters.Add("@market_date", SqlDbType.DateTime).Value = xchg_date;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(live_board);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return live_board;
        }
        //List of top 5 symbol
        public DataSet GetSymbolList(string symbol)
        {
            DataSet symbol_list = new DataSet();
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "p_sel_Securities";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters
                cmd.Parameters.Add("@symbol", SqlDbType.VarChar, 10).Value = symbol;

                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                adpt.Fill(symbol_list);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return symbol_list;
        }

        public void AddnewSecurities(string symbol, string stock_type, decimal ceiling,
            decimal floor, string security_name,DateTime issue_date, decimal par_value, 
            decimal open_price, decimal close_price, string board)
        {
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_upd_Securities";
                cmd.CommandType = CommandType.StoredProcedure;
                // Fill our parameters

                cmd.Parameters.Add("@SYMBOL", SqlDbType.VarChar, 20).Value = symbol;
                cmd.Parameters.Add("@STOCK_TYPE", SqlDbType.VarChar, 1).Value = stock_type;
                cmd.Parameters.Add("@CEILING", SqlDbType.Decimal).Value = ceiling;
                cmd.Parameters.Add("@FLOOR", SqlDbType.Decimal).Value = floor;
                cmd.Parameters.Add("@SECURITY_NAME", SqlDbType.NVarChar, 255).Value = security_name;
                cmd.Parameters.Add("@ISSUE_DATE", SqlDbType.DateTime).Value = issue_date;
                cmd.Parameters.Add("@PAR_VALUE", SqlDbType.Decimal).Value = par_value;
                cmd.Parameters.Add("@OPEN_PRICE", SqlDbType.Decimal).Value = open_price;
                cmd.Parameters.Add("@CLOSE_PRICE", SqlDbType.Decimal).Value = close_price;
                cmd.Parameters.Add("@BOARD", SqlDbType.VarChar, 10).Value = board;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
    }
}
